package com.fiap.assento;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import com.fiap.dao.ConnectionFactory;
import com.fiap.assento.AssentoBean;

public class AssentoDao 
{
	public void createAssentos(List<AssentoBean> listaAssentos) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		

		try {
			
			sql.append(" INSERT INTO ASSENTO_VOO ");
			sql.append(" (ID_VOO, ");
			sql.append("  ID_TRECHO_VOO, ");
			sql.append("  CODIGO_ASSENTO, ");
			sql.append("  CLASSE_ASSENTO, ");
			sql.append("  STATUS_ASSENTO) ");
			sql.append(" VALUES ( ?,?,?,?,?) ");
			
			pstmt = con.prepareStatement(sql.toString());

			for (AssentoBean assento : listaAssentos) {
				int i=0;
				
				pstmt.setLong(++i, assento.getIdVoo());
				pstmt.setLong(++i, assento.getIdTrecho());
				pstmt.setString(++i, assento.getCodigo());
				pstmt.setLong(++i, assento.getClasseAssento());
				pstmt.setLong(++i, assento.getStatusAssento());

				pstmt.addBatch();
			}
			pstmt.executeBatch();
			

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
		}
	}
	
	
	public AssentoBean update(AssentoBean assentoBean) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		int i=0;
		try {
			sql.append(" UPDATE assento_voo ");
			sql.append(" SET ");
			sql.append(" CLASSE_ASSENTO  = ?, ");
			sql.append(" STATUS_ASSENTO  = ?, ");
			sql.append(" WHERE ID_TRECHO_VOO = ? ");
			
			if(StringUtils.trimToNull(assentoBean.getCodigo())!=null){
			   sql.append(" AND  CODIGO_ASSENTO = ? ");
			}
			
			pstmt = con.prepareStatement(sql.toString());
			
			pstmt.setLong(++i, assentoBean.getIdTrecho());
			pstmt.setString(++i, assentoBean.getCodigo());
			
			pstmt.executeUpdate();

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
		}

		return assentoBean;
	}
	
	public void delete(AssentoBean assentoBean) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		int i=0;
		try {
			sql.append(" DELETE FROM ASSENTO_VOO ");
			sql.append(" WHERE ID_TRECHO_VOO  = ? ");
			sql.append("   AND CODIGO_ASSENTO = ? ");
			
			pstmt = con.prepareStatement(sql.toString());
		
			pstmt.setLong(++i, assentoBean.getIdTrecho());
			pstmt.setString(++i, assentoBean.getCodigo());
			
			pstmt.executeUpdate();

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
		}
	}
	
	public List<AssentoBean> list(AssentoBean assentoBeanFiltros) throws SQLException {
		Connection con = ConnectionFactory.getConnection();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		ResultSet rs = null;
		int i=0;
		List<AssentoBean> lista = new ArrayList<AssentoBean>();

		try {
			sql.append(" SELECT * FROM  ASSENTO_VOO ");
			sql.append(" WHERE ID_VOO    = ? ");
			sql.append(" AND ID_TRECHO_VOO = ?");
			
			if(StringUtils.trimToNull(assentoBeanFiltros.getCodigo())!=null){
			   sql.append(" AND  CODIGO_ASSENTO = ? ");
			}	
			
			
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setLong(++i, assentoBeanFiltros.getIdVoo()); 
			pstmt.setLong(++i, assentoBeanFiltros.getIdTrecho());
			
			if(StringUtils.trimToNull(assentoBeanFiltros.getCodigo())!=null){
				pstmt.setString(++i, assentoBeanFiltros.getCodigo());
			}
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				lista.add(this.mountBean(rs));
			}
			

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			pstmt.close();
			con.close();
			rs.close();
		}
		return lista;
	}
	
	public AssentoBean mountBean(ResultSet rs) throws SQLException{
		AssentoBean a = new AssentoBean();
		a.setIdVoo(rs.getLong("ID_VOO"));
		a.setIdTrecho(rs.getLong("ID_TRECHO_VOO"));
		a.setCodigo(rs.getString("CODIGO_ASSENTO"));
		a.setStatusAssento(rs.getLong("STATUS_ASSENTO"));
		a.setClasseAssento(rs.getLong("CLASSE_ASSENTO"));
		
		return a;
	}

}
